package com.qs.tx;

import org.springframework.jdbc.datasource.DataSourceTransactionManager;
import org.springframework.jdbc.datasource.DataSourceUtils;
import org.springframework.jdbc.datasource.DriverManagerDataSource;
import org.springframework.transaction.TransactionStatus;
import org.springframework.transaction.support.TransactionCallback;
import org.springframework.transaction.support.TransactionTemplate;

import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;

/**
 * 测试spring提供的编程式事务
 *
 * @author qinyupeng
 * @since 2021-06-28 01:07:00
 */
public class TransactionApp {

	private static final String url = "jdbc:mysql://localhost:3306/qr";
	private static final String username = "root";
	private static final String password = "12345678";

	public static void main(String[] args) {
		// 数据源
		DataSource dataSource = new DriverManagerDataSource(url, username, password);
		// 事务管理器
		DataSourceTransactionManager transactionManager = new DataSourceTransactionManager(dataSource);
		// 事务模板对象（编程式事务）
		TransactionTemplate transactionTemplate = new TransactionTemplate(transactionManager);
		transactionTemplate.execute(new TransactionCallback<Object>() {
			@Override
			public Object doInTransaction(TransactionStatus status) {
				// 获取数据库连接
				Connection connection = DataSourceUtils.getConnection(dataSource);
				Object savepoint = null;
				//Object savepoint = status.createSavepoint();
				try {
					// 执行新增
					PreparedStatement preparedStatement = connection.prepareStatement(
							"insert into t_account(account_name,amount,create_time) values(?,?,now())");
					preparedStatement.setString(1, "李四");
					preparedStatement.setDouble(2, 1000);
					preparedStatement.executeUpdate();

					// 设置保存点（回滚到这里）
					savepoint = status.createSavepoint();

					// 执行修改
					preparedStatement = connection.prepareStatement("update t_account set amount=amount-? where account_name=?");
					preparedStatement.setDouble(1, 1000);
					preparedStatement.setString(2, "张三");
					preparedStatement.executeUpdate();
					System.out.println("数据库所有操作执行成功。。。");
				} catch (SQLException e) {
					System.out.println("操作数据库失败。。。");
					if (savepoint != null) {
						status.rollbackToSavepoint(savepoint);
					} else {
						status.setRollbackOnly();
					}
				}

				return null;
			}
		});
	}
}
